BCO2149 Database Systems Assignment 2

Total Marks : 40
Instructions : Students are required to attempt each question.

Question 1

For each section in this question students are required to develop an Entity Relationship diagram from the following information. Relationships identified in your diagrams must show both minimum and maximum cardinality.

(a) Super Construction

It is an engineering company that works on several construction projects at any one time. Each project may require a number of employees to work on it and an employee must work on at least one project. The date an employee starts and ends works on a project must be recorded and the number of hours they worked on the project that day must also be recorded. An employee may work on the same project several times and this would be distinguished by the different start and end dates.

An employee must have at least one qualification and that qualification may be held by many other employees. For each qualification held by an employee, the year and the institution it was received from must be recorded.

An employee has at least one title and a title may be held by many employees. The construction company would like to record all the titles an employee has had within the company. The date to and date from should be recorded for each title an employee has had.

A project may require a number of tasks to be completed on it and that same task may be required to be completed on many other projects. The duration time for a particular task on a specific project needs to be recorded. The duration of a task may vary depending on the project.

A project is assigned one employee as its overall manager. An employee may manage many projects but a project is managed by one and only one employee.

Each task may require a number of parts to be used in order to complete it. A part may be used on many tasks or none at all. The number of parts used on a particular task may differ depending on the nature of the project. The number of parts used per task and project needs to be recorded.

Each employee must belong to one department and a department must have at least one employee assigned to it.

An employee may supervise one or more other employees and each employee is supervised by only one supervisor. (Employee)

An engineer must have at least one qualification and that qualification may be held by many other engineers. For each qualification held by an engineer, the year and the institution it was received from must be recorded.

(b) (i)

A doctor consults from one clinic only, but a clinic may have many doctors working from it. A patient makes an appointment to see a doctor on a specific day and time. A patient may consult with many doctors and a doctor must consult with at least one patient.

(ii)

What changes, if any, would need to be made to model developed above in (i), if the following to the scenario was included.
A doctor now works from many clinics and a clinic can have many doctors operating from it. When a patient makes a booking to see a doctor the time, day and clinic must be recorded.(8 + 4 = 12 marks)

Question 2 :

Students are required to develop an Entity Relationship diagram from the following scenario. (8 marks)

To Continue Click Here > BCO2149 Database Systems Assignment 2 semester 2.pdf